if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_GetCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_GetCount]
GO

/* Procedure sproc_GIA_DOAN_DUONG_GetCount*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_GetCount
AS
SELECT
	COUNT(*)
FROM
	[GIA_DOAN_DUONG]
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_Get]
GO

/* Procedure sproc_GIA_DOAN_DUONG_Get*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_Get
AS
SELECT
	--[MA_GDD],
	--[MA_DKS],
	--[GIA_DAT],
	--[NGUOI_KHOI_TAO],
	--[NGAY_KHOI_TAO],
	--[NGAY_CAP_NHAT],
	--[ACTIVE_FLAG]

*
FROM
	[GIA_DOAN_DUONG]
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_GetByMA_GDD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_GetByMA_GDD]
GO

/* Procedure sproc_GIA_DOAN_DUONG_GetByMA_GDD*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_GetByMA_GDD
@MA_GDD bigint
AS
SELECT
	--[MA_GDD],
	--[MA_DKS],
	--[GIA_DAT],
	--[NGUOI_KHOI_TAO],
	--[NGAY_KHOI_TAO],
	--[NGAY_CAP_NHAT],
	--[ACTIVE_FLAG]

*
FROM
	[GIA_DOAN_DUONG]
WHERE
	[MA_GDD] = @MA_GDD
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_GetByMA_GDD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_GetByMA_GDD]
GO

/* Procedure sproc_GIA_DOAN_DUONG_GetByMA_GDD*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_GetByMA_GDD
@MA_GDD		bigint
AS
SELECT
	[MA_GDD],
	[MA_DKS],
	[GIA_DAT],
	[NGUOI_KHOI_TAO],
	[NGAY_KHOI_TAO],
	[NGAY_CAP_NHAT],
	[ACTIVE_FLAG]
FROM
	[GIA_DOAN_DUONG]
WHERE
	[MA_GDD] = @MA_GDD
GO


if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_GetPaged]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_GetPaged]
GO

/* Procedure sproc_GIA_DOAN_DUONG_GetPaged*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_GetPaged
	@RecPerPage INT,
	@PageIndex INT
AS

DECLARE @FirstRec INT
DECLARE @LastRec INT

SET @FirstRec = (@PageIndex - 1)*@RecPerPage + 1
SET @LastRec = @PageIndex *@RecPerPage 

-- create temp table to paging
CREATE TABLE #tmp_paging_index
(
	recID		INT IDENTITY(1,1) NOT NULL,
	messageID	bigint
)

-- insert temp records
INSERT INTO #tmp_paging_index(messageID)
SELECT [MA_GDD]
FROM [GIA_DOAN_DUONG]


-- query out
SELECT *
FROM [GIA_DOAN_DUONG]
WHERE [MA_GDD]
IN (
	SELECT messageID 
	FROM #tmp_paging_index 
	WHERE recID >= @FirstRec AND recID <= @LastRec
)
DROP TABLE #tmp_paging_index

GO


if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_GetByMA_GDDPaged]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_GetByMA_GDDPaged]
GO

/* Procedure sproc_GIA_DOAN_DUONG_GetByMA_GDDPaged*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_GetByMA_GDDPaged
	@RecPerPage INT,
	@PageIndex INT,
	@MA_GDD bigint
AS

DECLARE @FirstRec INT
DECLARE @LastRec INT

SET @FirstRec = (@PageIndex - 1)*@RecPerPage + 1
SET @LastRec = @PageIndex *@RecPerPage 

-- create temp table to paging
CREATE TABLE #tmp_paging_index
(
	recID		INT IDENTITY(1,1) NOT NULL,
	messageID	bigint
)

-- insert temp records
INSERT INTO #tmp_paging_index(messageID)
SELECT [MA_GDD]
FROM [GIA_DOAN_DUONG]
WHERE [MA_GDD] = @MA_GDD


-- query out
SELECT *
FROM [GIA_DOAN_DUONG]
WHERE [MA_GDD] = @MA_GDD
AND [MA_GDD]
IN (
	SELECT messageID 
	FROM #tmp_paging_index 
	WHERE recID >= @FirstRec AND recID <= @LastRec
)
DROP TABLE #tmp_paging_index

GO


if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_Add]
GO

/* Procedure sproc_GIA_DOAN_DUONG_Add*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_Add
	@MA_GDD bigint OUTPUT
	,@MA_DKS bigint
	,@GIA_DAT float(8)
	,@NGUOI_KHOI_TAO uniqueidentifier
	,@NGAY_KHOI_TAO datetime
	,@NGAY_CAP_NHAT datetime
	,@ACTIVE_FLAG char(1)

AS

	INSERT INTO [GIA_DOAN_DUONG]
	(
		[MA_DKS],
		[GIA_DAT],
		[NGUOI_KHOI_TAO],
		[NGAY_KHOI_TAO],
		[NGAY_CAP_NHAT],
		[ACTIVE_FLAG]
	)
	VALUES
	(
		@MA_DKS,
		@GIA_DAT,
		@NGUOI_KHOI_TAO,
		@NGAY_KHOI_TAO,
		@NGAY_CAP_NHAT,
		@ACTIVE_FLAG
	)
	SELECT
		@MA_GDD = @@Identity

GO
if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_Update]
GO

/* Procedure sproc_GIA_DOAN_DUONG_Update*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_Update
	@MA_DKS bigint,
	@GIA_DAT float(8),
	@NGUOI_KHOI_TAO uniqueidentifier,
	@NGAY_KHOI_TAO datetime,
	@NGAY_CAP_NHAT datetime,
	@ACTIVE_FLAG char(1),
	@MA_GDD bigint

AS
UPDATE [GIA_DOAN_DUONG]
SET
	[MA_DKS] = @MA_DKS,
	[GIA_DAT] = @GIA_DAT,
	[NGUOI_KHOI_TAO] = @NGUOI_KHOI_TAO,
	[NGAY_KHOI_TAO] = @NGAY_KHOI_TAO,
	[NGAY_CAP_NHAT] = @NGAY_CAP_NHAT,
	[ACTIVE_FLAG] = @ACTIVE_FLAG
WHERE
	[MA_GDD] = @MA_GDD
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_Delete]
GO

/* Procedure sproc_GIA_DOAN_DUONG_Delete*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_Delete
	@MA_GDD bigint
AS
DELETE
FROM
	[GIA_DOAN_DUONG]
WHERE
	[MA_GDD] = @MA_GDD
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_getByMaDKS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_getByMaDKS]
GO

/* Procedure sproc_GIA_DOAN_DUONG_getByMaDKS*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_getByMaDKS
	@MA_DKS bigint
AS
	SELECT
		gdd.MA_GDD,
		gdd.MA_DKS,
		gdd.GIA_DAT,
		gdd.HISTORY,
		gdd.NGUOI_KHOI_TAO,
		gdd.NGAY_KHOI_TAO,
		gdd.NGAY_CAP_NHAT,
		gdd.ACTIVE_FLAG
	FROM
		GIA_DOAN_DUONG gdd
	WHERE gdd.MA_DKS = @MA_DKS AND gdd.ACTIVE_FLAG = 'Y' AND 
		gdd.NGAY_CAP_NHAT = (SELECT max(gdd1.NGAY_CAP_NHAT)
		                       FROM GIA_DOAN_DUONG gdd1)
GO

if exists (select * from sysobjects where id = object_id(N'[sproc_GIA_DOAN_DUONG_getByMaDKS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sproc_GIA_DOAN_DUONG_getByMaDKS]
GO

/* Procedure sproc_GIA_DOAN_DUONG_getByMaDKS*/
CREATE PROCEDURE sproc_GIA_DOAN_DUONG_getByMaDKS
	@MA_DKS bigint
AS
	SELECT
		gdd.MA_GDD,
		gdd.MA_DKS,
		gdd.GIA_DAT,
		gdd.NGUOI_KHOI_TAO,
		gdd.NGAY_KHOI_TAO,
		gdd.NGAY_CAP_NHAT,
		gdd.ACTIVE_FLAG
	FROM
		GIA_DOAN_DUONG gdd
	WHERE gdd.MA_DKS = @MA_DKS AND gdd.ACTIVE_FLAG = 'Y' AND 
		gdd.NGAY_CAP_NHAT = (SELECT max(gdd1.NGAY_CAP_NHAT)
		                       FROM GIA_DOAN_DUONG gdd1 WHERE gdd1.MA_GDD = gdd.MA_GDD)
GO